package DAL.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import DAL.Entites.Recommendation;

public class RecommendationJDBC {
	Connection conn;

	String tableName = "recommendations"; 

	public RecommendationJDBC(Connection connection) {
		this.conn = connection;
	}
	
	public void insert(int GameId, int UserId, String Recommendation) {
		// this is for selecting db incase we do insert first
		// ((DriverManagerDataSource)dataSource).setUrl("jdbc:mysql://localhost:3306/TestData");
		String SQL = String.format("insert into %s values(null, %s, %s, '%s')", tableName, UserId, GameId, Recommendation);
		
		try {
			Statement st = this.conn.createStatement();
			st.execute(SQL);
			st.close();
			System.out.println("Created Record");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return;
	}
	
	private Recommendation mapRow(ResultSet rs) throws SQLException {
		Recommendation recommendation = new Recommendation(
				rs.getInt("ID"), 
				rs.getInt("GameId"),
				rs.getInt("UserId"),
				rs.getString("Recommendation"));
		System.out.println("mapRow");

		return recommendation;
	}
	
	public List<Recommendation> listRecommendationsByGameId(int GameId) {
		List<Recommendation> recs = new ArrayList<Recommendation>();
		String SQL = String.format("select * from %s where GameID = %s ", tableName, GameId);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				recs.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return recs;
	}

	public Recommendation getRecommendationForUserAndGame(int userID, int gameId) {
		Recommendation recommendation = null;
		
		String SQL = String.format("select * from %s where GameID = %s and UserId = %s", tableName, gameId, userID);
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(SQL); 
			rs.next();
			recommendation = mapRow(rs);
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return recommendation;
	}
}
